﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using ResponsavelLib.Basic;
using System.Data;

namespace ResponsavelLib.Repositorio
{
    public class RepResponsavel
    {
        private SqlConnection conexao;
        private string stringConexao = @"Data Source=VITOR-PC\SQLEXPRESS;Initial Catalog=Escola; user id=sa; pwd=jva;";
        private static RepResponsavel instancia;

        private RepResponsavel()
        {
        }


        public static RepResponsavel getInstancia()
        {
            if (instancia == null)
            {
                instancia = new RepResponsavel();
                return instancia;
            }
            return instancia;
        }


        public void incluir(Responsavel obj)
        {

            conexao = new SqlConnection(stringConexao);

            try
            {

                SqlCommand sql = new SqlCommand("insert into Responsavel values (@NomeResp, @Endereço, @Telefone, @Email, @Sexo, @Filiação, @Categoria) \n insert into Aluno_Responsavel values ((select MAX(MatAluno) from Aluno),(select MAX(CodResp) from Responsavel))", conexao);

                sql.Parameters.AddWithValue("@NomeResp", obj.Nome);
                sql.Parameters.AddWithValue("@Endereço", obj.Endereco);
                sql.Parameters.AddWithValue("@Telefone", obj.Telefone);
                sql.Parameters.AddWithValue("@Email", obj.Email);
                sql.Parameters.AddWithValue("@Sexo", obj.Sexo);
                sql.Parameters.AddWithValue("@Filiação", obj.Filiacao);
                sql.Parameters.AddWithValue("@Categoria", obj.Categoria);

                conexao.Open();

                sql.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                conexao.Dispose();
            }

        }

        public void inserir(Responsavel obj,int matricula)
        {

            conexao = new SqlConnection(stringConexao);

            try
            {

                SqlCommand sql = new SqlCommand("insert into Responsavel values (@NomeResp, @Endereço, @Telefone, @Email, @Sexo, @Filiação, @Categoria) \n insert into Aluno_Responsavel values ((select MatAluno from Aluno where MatAluno = @MatAluno),(select MAX(CodResp) from Responsavel)) \n delete Aluno_Responsavel where MatAluno = @MatAluno and CodResp is null", conexao);

                sql.Parameters.AddWithValue("@MatAluno", matricula);
                sql.Parameters.AddWithValue("@NomeResp", obj.Nome);
                sql.Parameters.AddWithValue("@Endereço", obj.Endereco);
                sql.Parameters.AddWithValue("@Telefone", obj.Telefone);
                sql.Parameters.AddWithValue("@Email", obj.Email);
                sql.Parameters.AddWithValue("@Sexo", obj.Sexo);
                sql.Parameters.AddWithValue("@Filiação", obj.Filiacao);
                sql.Parameters.AddWithValue("@Categoria", obj.Categoria);

                conexao.Open();

                sql.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                conexao.Dispose();
            }

        }

        public List<Responsavel> pesquisar(int matricula)
        {
            conexao = new SqlConnection(stringConexao);

            try
            {
                List<Responsavel> lista = new List<Responsavel>();
                SqlCommand sql = new SqlCommand("select r.CodResp, r.NomeResp, r.Endereço, r.Telefone, r.Email, r.Sexo, r.Filiação, r.Categoria, a.MatAluno from Responsavel r inner join Aluno_Responsavel on r.CodResp=Aluno_Responsavel.CodResp inner join Aluno a on a.MatAluno=Aluno_Responsavel.MatAluno where Aluno_Responsavel.MatAluno=@MatAluno", conexao);

                sql.Parameters.AddWithValue("@MatAluno", matricula);

                conexao.Open();

                SqlDataReader dr = sql.ExecuteReader();

                while (dr.Read())
                {
                    Responsavel responsavel = new Responsavel();
                    responsavel.CodResp1 = Convert.ToInt32(dr["CodResp"]);
                    responsavel.Nome = dr["NomeResp"].ToString();
                    responsavel.Endereco = dr["Endereço"].ToString();
                    responsavel.Telefone = dr["Telefone"].ToString();
                    responsavel.Email = dr["Email"].ToString();
                    responsavel.Sexo = dr["Sexo"].ToString();
                    responsavel.Filiacao = dr["Filiação"].ToString();
                    responsavel.Categoria = dr["Categoria"].ToString();
                    lista.Add(responsavel);
                }
                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                conexao.Dispose();
            }
        }

        public void alterar(Responsavel obj, int matricula)
        {
            conexao = new SqlConnection(stringConexao);

            try
            {

                SqlCommand sql = new SqlCommand("update Responsavel set NomeResp=@NomeResp, Endereço=@endereço, Telefone=@Telefone, Email=@Email, Sexo=@Sexo, Filiação=@Filiação, Categoria=@Categoria where CodResp=@CodResp", conexao);

                sql.Parameters.AddWithValue("@MatAluno", matricula);
                sql.Parameters.AddWithValue("@CodResp", obj.CodResp1);
                sql.Parameters.AddWithValue("@NomeResp", obj.Nome);
                sql.Parameters.AddWithValue("@Endereço", obj.Endereco);
                sql.Parameters.AddWithValue("@Telefone", obj.Telefone);
                sql.Parameters.AddWithValue("@Email", obj.Email);
                sql.Parameters.AddWithValue("@Sexo", obj.Sexo);
                sql.Parameters.AddWithValue("@Filiação", obj.Filiacao);
                sql.Parameters.AddWithValue("@Categoria", obj.Categoria);

                conexao.Open();

                sql.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                conexao.Dispose();
            }
        }

        public List<Responsavel> listar()
        {

            conexao = new SqlConnection(stringConexao);

            try
            {
                List<Responsavel> lista = new List<Responsavel>();
                SqlCommand sql = new SqlCommand("select * from Responsavel order by CodResp", conexao);

                conexao.Open();

                SqlDataReader dr = sql.ExecuteReader();

                while (dr.Read())
                {
                    Responsavel responsavel = new Responsavel();
                    responsavel.CodResp1 = Convert.ToInt32(dr["CodResp"]);
                    responsavel.Nome = dr["NomeResp"].ToString();
                    responsavel.Endereco = dr["Endereço"].ToString();
                    responsavel.Telefone = dr["Telefone"].ToString();
                    responsavel.Email = dr["Email"].ToString();
                    responsavel.Sexo = dr["Sexo"].ToString();
                    responsavel.Filiacao = dr["Filiação"].ToString();
                    responsavel.Categoria = dr["Categoria"].ToString();
                    lista.Add(responsavel);
                }
                return lista;

            }
            catch (Exception e)
            {

                throw e;
            }


            finally
            {

                conexao.Dispose();

            }
        }

        public void excluir(int CodResp)
        {
            conexao = new SqlConnection(stringConexao);

            try
            {

                SqlCommand sql = new SqlCommand("delete from Aluno_Responsavel where CodResp=@CodResp \n delete from Responsavel where CodResp=@CodResp", conexao);

                sql.Parameters.AddWithValue("@CodResp", CodResp);

                conexao.Open();

                sql.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                conexao.Dispose();
            }
        }
    }
}
